﻿using Dapper;
using MySql.Data.MySqlClient;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using Publicuse.Entity;
using Publicuse.Entity.AttributeUtil;
using Publicuse.Entity.AttributeUtil.Validate;
using Publicuse.Util;

namespace Publicuse.DBCore
{
    /// <summary>
    /// 用来完成sql语句的缓存
    /// 每张表都是几个固定sql
    /// 泛型缓存:适合不需要释放的  体积小点  不同类型不同数据  
    /// </summary>
    public class SqlCacheBuilder<T> where T : new()
    {
        private static string mySqlQueryById = null;
        private static string MySqlInsert = null;
        private static string MySqlUpdate = null;
        private static string MySqlDelete = null;
        private static PropertyInfo[] files = null;
        private static MySqlParameter mySqlParameter = null;
        private static MySqlParameter[] mySqlParameters = null;
        private static DynamicParameters dynamicParameters = null;

        private static string schema = null;
        private static string keyId = null;

        private static SqlCacheBuilder<T> sqlCache = null;

        public MySqlParameter GetMySqlParameter
        {
            get { return mySqlParameter; }
        }

        public static SqlCacheBuilder<T> GSqlCacheBuilder
        {
            get { return sqlCache; }
        }

        public MySqlParameter[] GetMySqlParameters
        {
            get { return mySqlParameters; }
        }

        public DynamicParameters GetDynamicParameters
        {
            get { return dynamicParameters; }
        }

        static SqlCacheBuilder()
        {
            schema = TableAtributeUtil.GetTableSchema<T>();
            Type type = typeof(T);
            files = type.GetProperties();
            keyId = TableAtributeUtil.GetPrimaryKey<T>();
            var Key = string.Format("{0}=@{1}", keyId, keyId);
            string columnnames = string.Join(",", type.GetPropertiesWithNoInsert().Select(p => $"{p.GetMappingName()}"));
            string columnvalues = string.Join(",", type.GetPropertiesWithNoInsert().Select(p => $"@{p.GetMappingName()}"));
            var updatenames = String.Join(",", type.GetPropertiesWithNoInsert().Select(p => $"{p.GetMappingName()}=@{p.GetMappingName()}"));
            string sql = "select {0} from {1} where {2}";
            string columenames = String.Join(",", type.GetPropertiesWithNoQuery().Select(o => o.GetMappingName()));
            mySqlQueryById = string.Format(sql, columenames, type.GetMappingName(), Key);
            sql = "insert into {0} ({1}) values({2});select {3};";
            MySqlInsert = string.Format(sql, type.GetMappingName(), columnnames, columnvalues, " LAST_INSERT_ID()");
            MySqlUpdate = string.Format("update {0} set {1} where {2};", type.GetMappingName(), updatenames, Key);
            MySqlDelete = string.Format("delete from {0} where {1};", type.GetMappingName(), Key);
            sqlCache = new SqlCacheBuilder<T>();
            dynamicParameters = new DynamicParameters();
        }

        internal SqlCacheBuilder<T> GetSql(SqlCacheBuildertType sqlCacheBuildertType, T entity, out string msg, out string sql, long Id = 0)
        {
            Type type = typeof(T);
            sql = string.Empty;
            msg = string.Empty;
            if (entity != null)
            {
                if (!entity.ValidateModel(out msg))
                {
                    throw new Exception(msg);
                }
            }
            switch (sqlCacheBuildertType)
            {
                case SqlCacheBuildertType.MySqlQuery:
                    sql = mySqlQueryById;
                    mySqlParameter = new MySqlParameter(string.Format("@{0}", keyId), Id);
                    break;
                case SqlCacheBuildertType.MySqlInser:
                    sql = MySqlInsert;
                    mySqlParameters = type.GetPropertiesWithNoInsert().Select(p => new MySqlParameter(string.Format("@{0}", p.GetMappingName()), p.GetValue(entity) ?? DBNull.Value)).ToArray();
                    break;
                case SqlCacheBuildertType.MySqlUpdate:
                    sql = MySqlUpdate;
                    mySqlParameters = type.GetPropertiesWithNoUpdate().Select(p => new MySqlParameter(string.Format("@{0}", p.GetMappingName()), p.GetValue(entity) ?? DBNull.Value)).ToArray();
                    break;
                case SqlCacheBuildertType.MySqlDelete:
                    sql = MySqlDelete;
                    mySqlParameters = type.GetPropertiesWithKey().Select(p => new MySqlParameter(string.Format("@{0}", p.GetMappingName()), p.GetValue(entity) ?? DBNull.Value)).ToArray();
                    break;
                case SqlCacheBuildertType.MySqlDapperQuery:
                    sql = mySqlQueryById;
                    //dynamicParameters.Add(keyId, Id);
                    break;
                case SqlCacheBuildertType.MySqlDapperInser:
                    sql = MySqlInsert;
                    foreach (var item in type.GetPropertiesWithNoInsert())
                    {
                        dynamicParameters.Add(item.GetMappingName(), item.GetValue(entity));
                    }
                    break;
                case SqlCacheBuildertType.MySqlDapperUpdate:
                    sql = MySqlUpdate;
                    foreach (var item in type.GetPropertiesWithNoUpdate())
                    {
                        if (item.GetMappingName() == "uTime")
                        {
                            dynamicParameters.Add(item.GetMappingName(), DateTime.Now);
                        }
                        else
                        {
                            dynamicParameters.Add(item.GetMappingName(), item.GetValue(entity));
                        }
                    }
                    break;
                case SqlCacheBuildertType.MySqlDapperDelete:
                    sql = MySqlDelete;
                    foreach (var item in type.GetPropertiesWithKey())
                    {
                        dynamicParameters.Add(item.GetMappingName(), item.GetValue(entity));
                    }
                    break;
                default:
                    throw new Exception("未知执行SQL");
            }
            return this;
        }
    }

    internal enum SqlCacheBuildertType
    {
        MySqlQuery,

        MySqlInser,

        MySqlUpdate,

        MySqlDelete,

        MySqlDapperQuery,

        MySqlDapperInser,

        MySqlDapperUpdate,

        MySqlDapperDelete,

        SqlLiteQuery,

        SqlLiteInser,

        SqlLiteUpdate,

        SqlLiteDelete
    }
}
